#!/bin/bash
# File Name   : analysis_binlog.sh
# Author      : moshan
# Mail        : mo_shan@yeah.net
# Created Time: 2019-04-11 18:34:42
# Function    : Analysis binlog for MySQL
#########################################################################
work_dir='/data/git/analysis_binlog'
. ${work_dir}/function/logging.sh
localhost_ip="${5}"
log_file="${7}"
thread_num="${9}"
function f_analysis_binlog()
{
    mysqlbinlog="${1}"
    binlog_file="${2}"
    show="${3}"
    res_file="${4}"
    sort_pos="${5}"
    record_type="${6}"
    show_table_list="${7}"
    eval ${mysqlbinlog} --base64-output=decode-rows -vv ${binlog_file} 2>/dev/null|awk -v show_opt="${show}" -v sort_opt="${sort_pos}" -v record_type="${record_type}" -v stl="${show_table_list}" 'BEGIN {
        table = 1;
        trs_count = 0;
        trs_insert_count = 0;
        trs_delete_count = 0;
        trs_update_count = 0;
        show_first_time = "First Time";
        show_last_time = "Last Time";
        t_first_time = "";
        show_table = "Table";
        show_type = "Type";
        show_count = "affe(row)";
        db_name = "";
        t_name = "";
        show_total = "Trans(total)";
        show_insert = "Ins(s)";
        show_update = "Upd(s)";
        show_delete = "Del(s)";
        show_Tinsert = "Ins(T)";
        show_Tupdate = "Upd(T)";
        show_Tdelete = "Del(T)";
        show_dml_total = "Total(s)";
        s_type = ""; 
        s_count = 0;
        count = 0;
        start = 0;
        insert_count = 0;
        update_count = 0;
        delete_count = 0;
        flag = 0;
        all["all_insert"] = 0;
        all["all_update"] = 0;
        all["all_delete"] = 0;
        all["all_dml"] = 0;
        split(stl,s_tables,",");
        new_line = 0;
    }
    {
    if (show_opt == "detail" && start == 0)
        {
            printf "\033[35m%-50s%-20s%-20s%-10s%-10s%-10s%-10s%-10s%-10s\n\033[0m",show_table,show_first_time,show_last_time,show_type,show_count,show_insert,show_update,show_delete,show_total;
            start = 1;
        }
        if(match($0, /^#.*server id.*Table_map:.*mapped to number/)) 
        {
            new_line = 0;
            split($(NF-4),a,"`");
            db_name = a[2];
            t_name = a[4];
            t_name_tmp = (db_name"."t_name);
            flag = 1;
            t_time = (substr($1,2,6)" "$2);
            if ( length(t_first_time) == 0) 
            {
                t_first_time = t_time;
            }

            if(length(stl) != 0)
            {
                for(tmp in s_tables)
                {
                    if (t_name == s_tables[tmp])
                    {
                        new_line = 1;
                        next;
                    }
               }
            } else 
            {
                new_line = 1
            }
        }
        else if (match($0, /(### INSERT INTO .*\..*)/)) 
        {
            if (new_line == 0) 
            {
                next;
            }

            count = count+1;
            insert_count = insert_count+1;
            s_type = "INSERT"; 
            s_count = s_count+1;
        }
        else if (match($0, /(### UPDATE .*\..*)/)) 
        {
            if (new_line == 0) 
            {
                next;
            }

            count = count+1;
            update_count = update_count+1;
            s_type = "UPDATE"; 
            s_count = s_count+1;
        }
        else if (match($0, /(### DELETE FROM .*\..*)/)) 
        {
            if (new_line == 0) 
            {
                next;
            }

            count = count+1;
            delete_count = delete_count+1;
            s_type = "DELETE"; 
            s_count = s_count+1;
        }
        else if (match($0, /^(# at) /) && flag == 1 && s_count > 0) 
        {
            if (new_line == 0) 
            {
                next;
            }

            opt_insert = (t_name_tmp"_insert");
            opt_update = (t_name_tmp"_update");
            opt_delete = (t_name_tmp"_delete");
            opt_trs_insert = (t_name_tmp"_trs_insert");
            opt_trs_update = (t_name_tmp"_trs_update");
            opt_trs_delete = (t_name_tmp"_trs_delete");
            opt_first_time = (t_name_tmp"_first_time");
            opt_last_time = (t_name_tmp"_last_time");
            if (length(t_name_array[t_name_tmp]) == 0)
            {
                res[opt_insert] = 0;
                res[opt_update] = 0;
                res[opt_delete] = 0;
                res[opt_trs_insert] = 0;
                res[opt_trs_update] = 0;
                res[opt_trs_delete] = 0;
                res[opt_last_time] = "";
                res[opt_first_time] = t_time;
                t_name_array[t_name_tmp] = t_name_tmp;
            }

            if (s_type == "DELETE")
            {
                res[opt_delete] += s_count;
            }
            else if (s_type == "UPDATE")
            {
                res[opt_update] += s_count;
            }
            else if (s_type == "INSERT")
            {
                res[opt_insert] += s_count;
            }
            res[opt_last_time] = t_time;
            if (show_opt=="detail")
            {
                printf "\033[32m%-50s%-20s%-20s%-10s%-10s%-10s%-10s%-10s%-10s\n\033[0m",t_name_tmp,t_time,t_time,s_type,s_count,res[opt_insert],res[opt_update],res[opt_delete],trs_count;
            }
            s_type_tmp = s_type;
            s_type = ""; 
            s_count = 0; 
        }
        else if (match($0, /^(COMMIT)/)) 
        {
            if (new_line == 0) 
            {
                next;
            }

            if (s_type_tmp == "DELETE")
            {
                trs_delete_count += 1;
                res[opt_trs_delete] += 1;
            }
            else if (s_type_tmp == "UPDATE")
            {
                trs_update_count += 1;
                res[opt_trs_update] += 1;
            }
            else if (s_type_tmp == "INSERT")
            {
                trs_insert_count += 1;
                res[opt_trs_insert] += 1;
            }
            trs_count += 1;
            count = 0;
            insert_count = 0;
            update_count = 0; 
            delete_count = 0;
            s_type = ""; 
            s_count = 0; 
            flag = 0;
        }
    } END{
        if (show_opt == "detail")
        {
            print "";
        }
        printf "\033[35m%-50s%-20s%-20s%-10s%-10s%-10s%-10s%-10s%-10s\033[0m\n",show_table,show_first_time,show_last_time,show_type,show_count,show_insert,show_update,show_delete,show_dml_total;
        if (sort_opt == "update")
        {
            sort_pos = 9;
        }
        else if (sort_opt == "delete")
        {
            sort_pos = 10;
        }
        else if (sort_opt == "insert")
        {
            sort_pos = 8;
        }
        else
        {
            sort_pos = 11;
        }
        if (record_type =="count" || record_type == "c")
        {
            for (i in t_name_array)
            {
                opt_insert = (i"_insert");
                opt_update = (i"_update");
                opt_delete = (i"_delete");
                opt_first_time = (i"_first_time");
                opt_last_time = (i"_last_time");
                res_dml_total = res[opt_insert] + res[opt_delete] + res[opt_update];
                printf "\033[32m%-50s%-20s%-20s%-10s%-10s%-10s%-10s%-10s%-10s\n\033[0m",i,res[opt_first_time],res[opt_last_time],"DML","0",res[opt_insert],res[opt_update],res[opt_delete],res_dml_total | "sort -rn -k"sort_pos"";
                all["all_insert"] += res[opt_insert];
                all["all_update"] += res[opt_update];
                all["all_delete"] += res[opt_delete];
                all["all_dml"] += res_dml_total;
            }
        }
        else 
        {
            for (i in t_name_array)
            {
                opt_trs_insert = (i"_trs_insert");
                opt_trs_update = (i"_trs_update");
                opt_trs_delete = (i"_trs_delete");
                opt_first_time = ("_first_time");
                opt_last_time = (i"_last_time");
                res_trs_dml_total = res[opt_trs_insert] + res[opt_trs_delete] + res[opt_trs_update];
                printf "\033[32m%-50s%-20s%-20s%-10s%-10s%-10s%-10s%-10s%-10s\n\033[0m",i,res[opt_first_time],res[opt_last_time],"DML","0",res[opt_trs_insert],res[opt_trs_update],res[opt_trs_delete],res_trs_dml_total# | "sort -rn -k"sort_pos;
                all["all_insert"] += res[opt_trs_insert];
                all["all_update"] += res[opt_trs_update];
                all["all_delete"] += res[opt_trs_delete];
                all["all_dml"] += res_trs_dml_total;
            }
        }
        
        opt_last_time = t_time;
        printf "\033[33m%-50s%-20s%-20s%-10s%-10s%-10s%-10s%-10s%-10s\033[0m\n",show_table,show_first_time,show_last_time,show_type,show_count,show_insert,show_update,show_delete,show_total;
        printf "\r\033[32m%-50s%-20s%-20s%-10s%-10s%-10s%-10s%-10s%-10s\n\033[0m","The total",t_first_time,opt_last_time,"DML","0",all["all_insert"],all["all_update"],all["add_delete"],all["all_dml"]
        print "";
        printf "\033[34m%-50s%-20s%-20s%-10s%-10s%-10s%-10s%-10s%-10s\033[0m\n",show_table,show_first_time,show_last_time,show_type,show_count,show_Tinsert,show_Tupdate,show_Tdelete,show_total;
        printf "\033[32m%-50s%-20s%-20s%-10s%-10s%-10s%-10s%-10s%-10s\n\033[0m","Transaction",t_first_time,opt_last_time,"DML","0",trs_insert_count,trs_update_count,trs_delete_count,trs_count;
        print "";
    }' > ${res_file} #2>/dev/null
    if [ $? -eq 0 ]
    then
        f_logging "INFO" "THREAD_${thread_num}:Analysis completed --> ${binlog_file}. Analyze the results in this file '${res_file}'" "2"|tee -a ${log_file}
    else
        f_logging "WARN" "THREAD_${thread_num}:Analysis completed --> ${binlog_file}. The binlog file may be an empty transaction file."|tee -a ${log_file} 
    fi
    [ -f "${work_dir}/pid/${binlog_file}.pid" ] && rm -f ${work_dir}/pid/${binlog_file}.pid
    [ -f "${work_dir}/thread/${thread_num}" ] && rm -f ${work_dir}/thread/${thread_num}
}
f_analysis_binlog "${1}" "${2}" "${3}" "${4}" "${6}" "${8}" "${10}"
